﻿
////////////////////////////////////////////////////////////////////////////////////////////
// 文件名: SysTBViewBLL.gen.cs
//
// 模块名称: SysTBView
//
// 作者: yqf
//
// 历史记录（记录修改记录，修改文件后请添加修改记录，注明修改时间、修改人，修改内容）:
// 2013-01-22    yqf    创建文件
////////////////////////////////////////////////////////////////////////////////////////////

using System;
using System.Data;
using System.Data.Common;
using System.Text;
using Model;
using DAL;
using Common;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Net;
using System.IO;
using Model.Sys;
using System.Text.RegularExpressions;

namespace BLL
{
    /// <summary>
    /// SysTBView表业务逻辑层
    /// </summary>
    public partial class SysPublicBLL : BaseBLL
    {
        public SysPublicBLL(LoginInfo info)
        {
            loginInfo = info;
        }

        public List<SysNameValue> GetTableInfoList(string table)
        {
            try
            {
                return new SysPublicDAL(loginInfo).GetTableInfoList(table);
            }
            catch (Exception ex)
            {
                Logger.LogError("SysPublicBLL", "GetTableInfoList", AppError.EROR, 0, ex, "获取TableInfoList信息出错。", "");
                return new List<SysNameValue>();
            }
        }

        public DataTable GetTableInfoListMDP(string table)
        {
            return new SysPublicDAL(loginInfo).GetTableInfoListMDP(table);
        }

        public string TestConeciton(int dbType, string connectionString)
        {
            return new SysPublicDAL(loginInfo).TestConeciton(dbType, connectionString);
        }
        public static string FomatColumnType(string columnType, string columnLength, int columnPrecision, int decimalPlaces, string DBType)
        {
            string result = string.Empty;
            try
            {
                columnType = columnType.ToLower();
                if (columnType == "text" || columnType == "ntext")
                {
                    return "nvarchar(max)";
                }
                if (columnLength.Equals("0") || string.IsNullOrEmpty(columnLength) || columnLength.Equals("-1"))
                {

                    if (columnType == "varchar" || columnType == "nvarchar")
                    {
                        columnLength = "max";
                    }
                    if (columnType == "char")
                    {
                        columnLength = "8000";
                    }
                    if (columnType == "nchar")
                    {
                        columnLength = "4000";
                    }
                }
                if (decimalPlaces > columnPrecision && (decimalPlaces == 0 && columnPrecision == 0))
                {
                    columnPrecision = 38;
                    decimalPlaces = 4;
                }
                if (!string.IsNullOrEmpty(columnType))
                {
                    if (columnType.Equals("binary") || columnType.Equals("char") || columnType.Equals("datetime2") ||
                        columnType.Equals("datetimeoffset") || columnType.Equals("nchar")
                        || columnType.Equals("time") || columnType.Equals("varbinary")
                        || columnType.Equals("nvarchar") || columnType.Equals("varchar"))
                    {
                        result = string.Format("{0}({1})", columnType, columnLength);
                    }
                    else if (columnType.Equals("uniqueidentifier"))
                    {
                        result = "nvarchar(max)";
                    }
                    else if (columnType.Equals("double"))
                    {
                        result = "float";
                    }
                    else if (columnType.Equals("numeric"))
                    {
                        if (columnPrecision == 0)
                            columnPrecision = 22;
                        result = string.Format("{0}({1},{2})", columnType, columnPrecision, decimalPlaces);
                    }
                    else if (columnType.Equals("decimal"))
                    {
                        result = string.Format("{0}({1},{2})", columnType, columnPrecision, decimalPlaces);
                    }
                    else
                    {
                        result = columnType;
                    }
                }
                else
                {
                    result = "";
                }
            }
            catch (Exception)
            {
                throw;
            }
            return result;
        }

        public DataTable GetDataTable(string sql)
        {
            return new SysPublicDAL(loginInfo).GetDataTable(sql);
        }
        public bool CheckType(string typeName, string columnName)
        {
            return new SysPublicDAL(loginInfo).CheckType(typeName, columnName);
        }

        public int ExistDB(string dbName, bool isDisplay)
        {
            DB db = new DB();
            return db.ExistDB(dbName, isDisplay);
        }
        public DBDeploy GetDBInfo(string dbName)
        {
            DB db = new DB();
            return db.GetDBInfo(dbName);
        }
        public bool DropDataBase(string dbName)
        {
            DB db = new DB();
            return db.DropDataBase(dbName);
        }
        public bool RenameDB(string dbName, string newDBName)
        {
            DB db = new DB();
            return db.RenameDB(dbName, newDBName);
        }
        public bool UpDataBaseStatus(string dbStatus, string dbName)
        {
            DB db = new DB();
            return db.UpDataBaseStatus(dbStatus, dbName);
        }
        public bool ConnectDB(string dbStatus, string userName, string dbName)
        {
            DB db = new DB();
            return db.ConnectDB(dbStatus, userName, dbName);
        }
        public bool DBJob(string dbStatus, string dbName)
        {
            return new SysPublicDAL(loginInfo).DBJob(dbStatus, dbName);
        }
        public bool CreateDB(string dbName, string a, string b, string c, string d, string dbType)
        {
            try
            {
                DB db = new DB();
                return db.CreateDB(dbName, a, b, c, d, dbType);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public bool ModifyModel(string path)
        {
            return DB.ModifyModel(path);
        }
        public bool CreateJob(string sql)
        {
            DB db = new DB();
            return db.CreateJob(sql);
        }


        /// <summary> 
        /// 读取FTP站点下 文件，返回FTP站点上的根目录下的所有xml文件名称       
        /// </summary> 
        /// <param name="ftp">FTP站点</param>
        /// <param name="username">用户名</param>    
        /// <param name="password">密码</param>        
        /// <returns></returns> 
        public static string readerFtp(string ftp, string username, string password, out List<string> dirList)
        {
            dirList = new List<string>();
            try
            {
                FtpWebRequest reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri(ftp));
                reqFTP.Credentials = new NetworkCredential(username, password);
                reqFTP.KeepAlive = false;
                reqFTP.Method = WebRequestMethods.Ftp.ListDirectory;
                WebResponse response = reqFTP.GetResponse();
                StreamReader sr = new StreamReader(response.GetResponseStream(), System.Text.Encoding.Default);
                string a = sr.ReadToEnd();
                if (!string.IsNullOrEmpty(a))
                {
                    a = a.Replace("\r\n", "$");
                    string[] aArr = a.Split('$');
                    foreach (string s in aArr)
                    {
                        dirList.Add(s);
                    }
                }
                response.Close();
                return "1";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }
        public static Stream readerFtp(string ftp, string username, string password)
        {
            try
            {
                FtpWebRequest reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri(ftp));
                reqFTP.Credentials = new NetworkCredential(username, password);
                reqFTP.KeepAlive = false;
                reqFTP.Method = WebRequestMethods.Ftp.ListDirectory;
                WebResponse response = reqFTP.GetResponse();
                Stream st = response.GetResponseStream();
                response.Close();
                return st;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary> 
        /// 下载FTP站点下 文件 
        /// </summary> 
        /// <param name="ftp">FTP站点</param>
        /// <param name="username">用户名</param>    
        /// <param name="password">密码</param>     
        /// <param name="savePath">保存路径</param>        
        /// <returns></returns> 
        public static string DownloadFtpFile(string ftp, string username, string password, string savePath)
        {
            try
            {
                FtpWebRequest reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri(ftp));
                reqFTP.Credentials = new NetworkCredential(username, password);
                reqFTP.KeepAlive = false;
                reqFTP.Method = WebRequestMethods.Ftp.DownloadFile;
                WebResponse response = reqFTP.GetResponse();

                Stream ftpStream = response.GetResponseStream();
                long cl = response.ContentLength;
                int bufferSize = 2048;
                int readCount;
                byte[] buffer = new byte[bufferSize];

                FileStream outputStream = new FileStream(savePath, FileMode.Create);
                readCount = ftpStream.Read(buffer, 0, bufferSize);
                while (readCount > 0)
                {
                    outputStream.Write(buffer, 0, readCount);
                    readCount = ftpStream.Read(buffer, 0, bufferSize);
                }
                ftpStream.Close();
                outputStream.Close();
                response.Close();

                return "1";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        public static string DataTableToString(DataTable dt, int width, string sortField, string sortBy, bool checkBox, bool fullText, bool sortor)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("");
            sb.AppendLine(string.Format("<table id=\"tbSoutceTableData\" class=\"pagTableNofixed\" cellspacing=\"0\" cellpadding=\"0\" style=\"{0}\">", (width > 0 ? string.Format("width: {0}px;", width) : string.Empty)));
            sb.AppendLine("<tbody>");
            sb.AppendLine(" <tr class=\"pagTablehd\">");
            int columnWidth = 100 / dt.Columns.Count;
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                if (i == 0 && checkBox)
                {
                    sb.AppendLine("<td width='2%'><input type='checkbox' class='selectAll' onclick='selectAll(\"tbSoutceTableData\")'></td>");
                }
                string textalign = "";
                if (dt.Columns[i].DataType.Equals(typeof(int))
                    || dt.Columns[i].DataType.Equals(typeof(float))
                    || dt.Columns[i].DataType.Equals(typeof(DateTime)))
                {
                    textalign = "center";
                }
                else
                {
                    textalign = "left";
                }
                string columnName = dt.Columns[i].ColumnName;
                string sortHead = "";
                string csscursor = "";
                if (sortor)
                {
                    csscursor = "csscursor";
                    sortHead = string.Format(" onclick=\"initSourceTableData('{0}','{1}')\"",
                        columnName, sortField.Equals(columnName, StringComparison.CurrentCultureIgnoreCase) ? (sortBy.Equals("desc") ? "asc" : "desc") : "desc");
                }
                sb.AppendLine(string.Format("<td class=\"DataGrid2ViewName {3} cssnowrap\" style=\"width: {0}%; text-align: {1};\" {2}>"
                    , columnWidth, textalign, sortHead, csscursor));

                sb.AppendLine(string.Format("{1}{0}", columnName
                    , sortField.Equals(columnName, StringComparison.CurrentCultureIgnoreCase) ? (sortBy.Equals("desc") ? "<img src='/images/datagrid_sort_asc.gif'>" : "<img src='/images/datagrid_sort_desc.gif'>") : ""
                    ));
                sb.AppendLine("</td>");
            }
            sb.AppendLine(" </tr>");
            int rowNumber = 0;
            foreach (DataRow rowItem in dt.Rows)
            {
                if (rowNumber++ % 2 == 0)
                {
                    sb.AppendLine(" <tr>");
                }
                else
                {
                    sb.AppendLine(" <tr class=\"tr_c2\">");
                }
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    string textalign = "";
                    if (dt.Columns[i].DataType.Equals(typeof(int))
                        || dt.Columns[i].DataType.Equals(typeof(float))
                        || dt.Columns[i].DataType.Equals(typeof(DateTime)))
                    {
                        textalign = "center";
                    }
                    else
                    {
                        textalign = "left";
                    }
                    if (i == 0 && checkBox)
                    {
                        sb.AppendLine("<td><input type='checkbox' class='ck_" + rowItem[0] + "' value='" + rowItem[0] + "' onclick='chooseItem(\"" + rowItem[0] + "\")'/></td>");
                    }
                    var value = rowItem[i] == null || string.IsNullOrEmpty(rowItem[i].ToString()) ? "&nbsp;" : rowItem[i].ToString();
                    int vlength = value.Length;
                    if (vlength > 200 && !fullText)
                        value = value.Substring(0, 200) + "...";
                    var title = value.Equals("&nbsp;") ? "" : string.Format("内容：{0}\n长度：{1}", value, vlength);
                    sb.AppendLine(string.Format("     <td style=\"text-align: {0};\" title=\"{1}\">", textalign, title));
                    //if (dt.Columns[i].DataType.Equals(typeof(DateTime)))
                    {
                        sb.AppendLine("     <nobr>");
                    }
                    if (vlength > 50 && !fullText)
                        value = value.Substring(0, 50) + "...";
                    sb.AppendLine(string.Format("         {0}", value));
                    //if (dt.Columns[i].DataType.Equals(typeof(DateTime)))
                    {
                        sb.AppendLine("     </nobr>");
                    }
                    sb.AppendLine("     </td>");
                }
                sb.AppendLine(" </tr>");
            }
            sb.AppendLine("</tbody>");
            sb.AppendLine("</table>");
            return sb.ToString();
        }

        public static string DataTableToString(DataTable dt, Dictionary<string, string> columnNameDics, string sortField, string sortBy, string onColumnClick)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("");
            sb.AppendLine("<table id=\"tbSoutceTableData\" class=\"pagTableNofixed\" cellspacing=\"0\" cellpadding=\"0\">");
            sb.AppendLine("<tbody>");
            sb.AppendLine(" <tr class=\"pagTablehd\">");
            int width = 100 / dt.Columns.Count;
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                string textalign = "";
                if (dt.Columns[i].DataType.Equals(typeof(int))
                    || dt.Columns[i].DataType.Equals(typeof(float))
                    || dt.Columns[i].DataType.Equals(typeof(DateTime)))
                {
                    textalign = "center";
                }
                else
                {
                    textalign = "left";
                }
                string columnName = dt.Columns[i].ColumnName;
                string columnCaption = dt.Columns[i].ColumnName;
                if (columnNameDics != null && columnNameDics.ContainsKey(columnName.ToUpper()))
                {
                    if (!string.IsNullOrEmpty(columnNameDics[columnName.ToUpper()]))
                    {
                        columnCaption = columnNameDics[columnName.ToUpper()];
                    }
                }
                sb.AppendLine(string.Format("     <td class=\"DataGrid2ViewName csscursor\" style=\"width: {0}%; text-align: {1};\" onclick=\"{5}('{2}',{4})\">"
                    , width, textalign
                    , columnName
                    , columnCaption
                    , sortField.Equals(columnName, StringComparison.CurrentCultureIgnoreCase) ? (sortBy.Equals("1") ? "0" : "1") : "1"
                    , !string.IsNullOrEmpty(onColumnClick) ? onColumnClick : "initSourceTableData"
                    ));


                sb.AppendLine(string.Format("         {1}{0}", columnCaption
                    , sortField.Equals(columnName, StringComparison.CurrentCultureIgnoreCase) ? (sortBy.Equals("1") ? "<img src='/images/datagrid_sort_asc.gif'>" : "<img src='/images/datagrid_sort_desc.gif'>") : ""
                    ));
                sb.AppendLine("     </td>");
            }
            sb.AppendLine(" </tr>");
            int rowNumber = 0;
            foreach (DataRow rowItem in dt.Rows)
            {
                if (rowNumber++ % 2 == 0)
                {
                    sb.AppendLine(" <tr>");
                }
                else
                {
                    sb.AppendLine(" <tr class=\"tr_c2\">");
                }
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    string textalign = "";
                    if (dt.Columns[i].DataType.Equals(typeof(int))
                        || dt.Columns[i].DataType.Equals(typeof(float))
                        || dt.Columns[i].DataType.Equals(typeof(DateTime)))
                    {
                        textalign = "center";
                    }
                    else
                    {
                        textalign = "left";
                    }

                    var value = rowItem[i] == null || string.IsNullOrEmpty(rowItem[i].ToString()) ? "&nbsp;" : rowItem[i].ToString();
                    int vlength = value.Length;
                    if (vlength > 200)
                        value = value.Substring(0, 200) + "...";
                    var title = value.Equals("&nbsp;") ? "" : string.Format("内容：{0}\n长度：{1}", value, vlength);
                    sb.AppendLine(string.Format("     <td style=\"text-align: {0};\" title=\"{1}\">", textalign, title));
                    //if (dt.Columns[i].DataType.Equals(typeof(DateTime)))
                    {
                        sb.AppendLine("     <nobr>");
                    }
                    if (vlength > 50)
                        value = value.Substring(0, 50) + "...";
                    sb.AppendLine(string.Format("         {0}", value));
                    //if (dt.Columns[i].DataType.Equals(typeof(DateTime)))
                    {
                        sb.AppendLine("     </nobr>");
                    }
                    sb.AppendLine("     </td>");
                }
                sb.AppendLine(" </tr>");
            }
            sb.AppendLine("</tbody>");
            sb.AppendLine("</table>");
            return sb.ToString();
        }

        public static string GetSourceTableHeadData(DataColumnCollection columnList)
        {
            StringBuilder sb = new StringBuilder();
            StringBuilder sbOpt = new StringBuilder();
            IList<Operator> optList = OperatorBLL.GetList();
            sbOpt.Append("<option value=''>-请选择-</option>");
            foreach (Operator o in optList)
            {
                sbOpt.Append("<option>" + o.LegalOperator + "</option>");
            }
            sb.Append("<table class='pagTable' cellpadding='0' cellspacing='0'>");
            foreach (DataColumn dc in columnList)
            {
                sb.Append(string.Format("<tr id='{0}'><td>{0}</td><td width='100px'><select class='sel_{0}'>{1}</select></td><td width='160px'><input width='150px' type='text' class='txt_{0}'></td></tr>", dc.ColumnName, sbOpt.ToString()));
            }
            sb.Append("</table>");
            return sb.ToString();
        }

        /// <summary> 
        /// DataTable分页 
        /// </summary> 
        /// <param name="dt">DataTable</param>
        /// <param name="PageIndex">页索引,注意：从1开始</param>
        /// <param name="PageSize">每页大小</param>
        /// <returns>分好页的DataTable数据</returns>
        public static DataTable GetPagedTable(DataTable dt, int PageIndex, int PageSize)
        {
            if (PageIndex == 0) { return dt; }
            DataTable newdt = dt.Copy();
            newdt.Clear();
            int rowbegin = (PageIndex - 1) * PageSize;
            int rowend = PageIndex * PageSize;
            if (rowbegin >= dt.Rows.Count)
            { return newdt; }
            if (rowend > dt.Rows.Count)
            { rowend = dt.Rows.Count; }
            for (int i = rowbegin; i <= rowend - 1; i++)
            {

                DataRow newdr = newdt.NewRow();
                DataRow dr = dt.Rows[i];
                foreach (DataColumn column in dt.Columns)
                {
                    newdr[column.ColumnName] = dr[column.ColumnName];
                }
                newdt.Rows.Add(newdr);
            }
            return newdt;
        }
        /// <summary>
        /// 取用户对实例的权限
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="dbName"></param>
        /// <returns>0没任何权限，1只读，2管理</returns>
        public static int GetUserRole(string userName, string dbName)
        {
            if (string.IsNullOrEmpty(userName) || string.IsNullOrEmpty(dbName))
                return 0;
            LoginInfo loginInfos = new LoginInfo();
            RoleBll roleBll = new RoleBll(loginInfos);
            List<RoleDBInfo> roleList = roleBll.GetUserRole(userName, dbName);
            int roletg = 0;
            foreach (RoleDBInfo roleItem in roleList)
            {
                if (roleItem.IsManagement == 1)
                    return 2;
                else if (roleItem.IsView == 1)
                    roletg = 1;
            }
            return roletg;
        }
        /// <summary>
        /// 取用户对实例的启用禁用权限
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="dbName"></param>
        /// <returns>0没有菜单,1没有，2有</returns>
        public static int GetUserEnableRole(string userName, string dbName)
        {
            if (string.IsNullOrEmpty(userName) || string.IsNullOrEmpty(dbName))
                return 0;
            LoginInfo loginInfos = new LoginInfo();
            RoleBll roleBll = new RoleBll(loginInfos);
            List<RoleDBInfo> roleList = roleBll.GetUserRole(userName, dbName);
            if (roleList.Count == 0)
                return 0;
            int roletg = 1;
            foreach (RoleDBInfo roleItem in roleList)
            {
                if (roleItem.IsEnable == 1)
                    return 2;
            }
            return roletg;
        }/// <summary>
        /// 取用户对实例的连接状态
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="dbName"></param>
        /// <returns>0可连接,1断开连接</returns>
        public static int GetUserDBConn(string userName, string dbName)
        {
            if (string.IsNullOrEmpty(userName) || string.IsNullOrEmpty(dbName))
                return 1;
            LoginInfo loginInfos = new LoginInfo();
            RoleBll roleBll = new RoleBll(loginInfos);
            return roleBll.GetUserDBConn(userName, dbName);
        }

        /// <summary>
        /// 检查字段类型
        /// </summary>
        /// <param name="list"></param>
        /// <param name="dbName"></param>
        /// <returns></returns>
        public static string CheckType(IList<ETL_Source_View_ColumnEdit> list)
        {
            string type = string.Empty;
            int length = 0;
            int precision = 0;
            int scale = 0;

            string regexString1 = @"^[A-Za-z0-9]+\(+[0-9]+\,+[0-9]+\)+$";//验证例如:decimal(18,0)类型
            string regexString2 = @"^[A-Za-z0-9]+\(+-?\d+\)+$";//验证例如:nvarchar(50)类型
            string regexString3 = @"^[A-Za-z0-9]+$";//验证例如:int类型
            DataTable dt = new DataTable();
            DataColumn dc0 = new DataColumn("ColumnID");
            DataColumn dc1 = new DataColumn("ColumnName");
            DataColumn dc2 = new DataColumn("ColumnType");
            DataColumn dc3 = new DataColumn("columnLength");
            DataColumn dc4 = new DataColumn("columnPresicion");
            DataColumn dc5 = new DataColumn("columnScale");
            dt.Columns.Add(dc0);
            dt.Columns.Add(dc1);
            dt.Columns.Add(dc2);
            dt.Columns.Add(dc3);
            dt.Columns.Add(dc4);
            dt.Columns.Add(dc5);
            int i = 0;
            foreach (ETL_Source_View_ColumnEdit item in list)
            {

                string typeName = item.ColumnType.ToLower().Replace("max", "-1");
                if (Regex.IsMatch(typeName, regexString1))
                {
                    typeName = typeName.Replace("(", "|").Replace(",", "|").Replace(")", "");
                    string[] typeArray = typeName.Split('|');
                    type = typeArray[0];
                    length = int.Parse(typeArray[1]);
                    precision = int.Parse(typeArray[1]);
                    scale = int.Parse(typeArray[2]);
                }
                else if (Regex.IsMatch(typeName, regexString2))
                {
                    typeName = typeName.Replace("(", "|").Replace(")", "");
                    string[] typeArray = typeName.Split('|');
                    type = typeArray[0];
                    length = int.Parse(typeArray[1]);
                }
                else if (Regex.IsMatch(typeName, regexString3))
                {
                    type = typeName;
                }
                else
                {
                    return string.Format("字段: {0} 的字段类型 {1} 格式不正确，请检查!", item.ColumnName, item.ColumnType);
                }


                DataRow dr = dt.NewRow();
                dr[0] = i;
                dr[1] = item.ColumnName;
                dr[2] = type;
                dr[3] = length;
                dr[4] = precision;
                dr[5] = scale;
                dt.Rows.Add(dr);
                i++;
            }

            LoginInfo loginInfos = new LoginInfo();
            return new SysPublicDAL(loginInfos).CheckType(dt);
        }
    }
}